package top.wardyan.sqlchecker.util;

import java.io.File;
import java.nio.charset.Charset;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import com.jfinal.kit.Prop;
import com.jfinal.kit.PropKit;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.file.FileReader;
import cn.hutool.core.lang.Console;
import cn.hutool.core.util.ReUtil;
import top.wardyan.base.common.util.EncodingDetect;
import top.wardyan.base.common.util.MD5Util;
import top.wardyan.sqlchecker.po.WSqlFile;

public class CustomizedReadSQLFileUtil {

	private static final cn.hutool.log.Log log = cn.hutool.log.Log.get();

	private static final Map<String, String> REGS_ERRORS = new HashMap<String, String>(){{
//		this.put("USE风险1", "^(use )+");
//		this.put("USE风险2", "[^\\S]use[\\s,]");
		Prop p = PropKit.use("config-sqlchecker-errors.txt");
		Set<Object> names = p.getProperties().keySet();
		
		for(Object name : names) {
			String key = String.valueOf(name);
			String regex = p.get(key);
			this.put(key, regex);
		}
	}};
	private static final Map<String, String> REGS_WARNINGS = new HashMap<String, String>(){{
		Prop p = PropKit.use("config-sqlchecker-warnings.txt");
		Set<Object> names = p.getProperties().keySet();
		
		for(Object name : names) {
			String key = String.valueOf(name);
			String regex = p.get(key);
			this.put(key, regex);
		}
	}};
	private static final Map<String, String> REGS_INFOS = new HashMap<String, String>(){{
		Prop p = PropKit.use("config-sqlchecker-infos.txt");
		Set<Object> names = p.getProperties().keySet();
		
		for(Object name : names) {
			String key = String.valueOf(name);
			String regex = p.get(key);
			this.put(key, regex);
		}
		
	}};
	// 支持的编码
	private static final List<String> ENCODINGS = new ArrayList<>() {{
		this.add("gb2312");
		this.add("unicode");
	}};
	
	
	private static final String regex = "^(\\d{8}|\\d{6})";
	private static final Pattern pattern = Pattern.compile(regex);


	/**
	 * 文件排序
	 * 如果是文件夹, 就以日期来排序; 如果是文件, 则以前缀的数字来排序
	 * @param files
	 * @return
	 */
	private static File[] sort(File[] files) {
		
		return Arrays.asList(files).stream().sorted(new Comparator<File>() {
			@Override
			public int compare(File f1, File f2) {
				// 文件夹一定优先于文件f
				// 
				int rs = 0;
				if(f1.isDirectory() && f2.isDirectory()) {
					// 两个都是文件夹	// 如果一个是完整的年, 另一个实际简短的年, 就把完整的年去掉前两位
					// NumberUtil.parse
					Integer date1 = ReUtil.getFirstNumber(f1.getName());
					Integer date2 = ReUtil.getFirstNumber(f2.getName());
					if(date1 == null) {
						date1 = 0;
					}
					if(date2 == null) {
						date2 = 0;
					}
					if(date1 > 999999) {
						date1 = date1 % 1000000;
					}
					if(date2 > 999999) {
						date2 = date2 % 1000000;
					}
					rs = Integer.compare(date1,  date2);
					
				}else if(f1.isDirectory() || f2.isDirectory()) {
					// 一个是文件夹
					if(f1.isDirectory()) {
						rs = -1;
					}else {
						rs = 1;
					}
				}else {
					// 两个都不是文件夹
					Integer date1 = ReUtil.getFirstNumber(f1.getName());
					Integer date2 = ReUtil.getFirstNumber(f2.getName());
					if(date1 == null) {
						date1 = 0; 
					}
					if(date2 == null) {
						date2 = 0;
					}
					rs = Integer.compare(date1,  date2);
				}
				return rs;
			}
		}).collect(Collectors.toList()).toArray(new File[] {});
		
		// return files;
	}
	
	
	/**
	 * 从文件夹找所有者
	 * @param folder
	 * @return
	 */
	private static String getOwner(String folder) {
		// 去掉前置的日期
//		String owner = ReUtil.delFirst("[0-9\\_]*", folder);
//		// 去掉后面可能的后缀
//		owner = ReUtil.delLast("[\\_]+", owner);
		if(folder == null) {
			folder = "";
		}
		String owner = folder.replaceAll("^(\\d{6,8}_?)|(_[^_]*$)", "");
		if(owner.contains("_")) {
			owner = owner.substring(0, owner.indexOf("_"));
		}
		return owner;
	}
	
	
	private static String getObjectName(String text) {
		// 首先找出: CREATE|ALTER开头的行, 然后再来匹配名称, 如果带有dbo要去掉
		// text参数的格式类似: CREATE PROC dbo.kp_device_list
		String rs = "";
		String[] lines = text.split("\\r?\\n");

		
		for(String line : lines) {

			// 匹配: CREATE|ALTER开头的行
			line = line.trim().toLowerCase();
			if(	(line.startsWith("create") || line.startsWith("alter") || (line.length() > 10 && (line.substring(1).startsWith("create") || line.substring(1).startsWith("alter"))))
					&& (line.contains("proc") || line.contains("view") || line.contains("function") || line.contains("trigger"))
					&& !line.contains("enable")
					&& !line.contains("disable")
					) {
				// 匹配: CREATE|ALTER开头的行, 然后再来匹配名称, 如果带有dbo要去掉
				// text参数的格式类似: CREATE PROC dbo.kp_device_list
//				String[] words = line.split("\\s+");
//				rs = words[words.length - 1];
				
				rs = ReUtil.getGroup0("(([a-z][pvfr][a-z]{0,1}\\_[a-z\\_0-9]+))", line);
				if(rs == null) {
					rs = "";
				}
				break;
			}
		}
		
		rs = rs.trim();
		if(rs.contains("[")) {
			rs = rs.replace("[", "");
		}
		if(rs.contains("]")) {
			rs = rs.replace("]", "");
		}
		if(rs.startsWith("dbo.")) {
			rs = rs.substring(4);
		}
		
		return rs;
	}
	
	/**
	 * 读取路径内的SQL文件
	 * @param path
	 * @return
	 */
	public static List<WSqlFile> read(String location, Timestamp begintime){
		List<WSqlFile> rs = new ArrayList<>();
		long begintimelong = 0L;
		if(begintime != null) {
			begintimelong = begintime.getTime();
		}
		
		
		//log.info("检查目录:{}", location);

		// 排除: 以debug.sql结尾的都过滤掉
		if(location.toLowerCase().contains("debug") || location.toLowerCase().contains("error") || location.toLowerCase().endsWith("err")) {
			log.info("debug文件,跳过... {}", location);
			return rs;
		}
		
		// 列出下级
//		List<String> files = new ArrayList<>();
//		try {
//			files = FileUtil.listFileNames(location);
//			System.out.println(files);
//			FileUtil.files
//		}catch(Exception e) {
//			log.error(e);
//		}
//		// 怎么排序?
//		for(String fileName : files) {
//			File file = FileUtil.file(fileName);
//			if(file.isDirectory()) {
//				log.info("是目录,要查下级目录.{}", location);
//				//listFiles(location);
//			}
//		}
//		
		File file = new File(location);
		// 在这里肯定是目录
//		if(file.isDirectory()) {
//			// 是目录,列出下级目录/文件
//			Console.log(file.listFiles());
//		}
		//String regex = "^(\\d{6}|\\d{8})";
		Timestamp date = new Timestamp(file.lastModified());
		Matcher matcher = pattern.matcher(file.getName());
        if (matcher.find()) {
            //System.out.println("Original: " + str + " -> Extracted Date: " + matcher.group(1));
        	//date = DateUtil.parse(matcher.group(1));
        	String s = matcher.group(1);
        	if(s.length() < 8) {
        		s = "20" + s;
        	}
        	cn.hutool.core.date.DateTime d = DateUtil.parse(s);
        	if(d != null) {
        		date = new Timestamp(d.getTime());
        	}
        }
		File[] subs = file.listFiles();
		if(subs != null && subs.length > 0) {
			// 排序	// 如果是文件夹, 就以日期来排序; 如果是文件, 则以前缀的数字来排序
			subs = sort(subs);
			for(File f : subs) {
				String filename = location + "/" + f.getName();

				String fn = file.getName() + "/" + f.getName();
				
				// log.info(filename);
				if(f.isDirectory()) {
					//log.info("是目录,要查下级目录.{}", filename);
					rs.addAll(read(filename, begintime));
				}else {
					String msg = null;
					if(f.lastModified() < begintimelong) {
						log.info("文件的最后修改时间早于检查的起始时间,跳过:{}", fn);
						continue;
					}
					
					// 如果不是.sql文件就跳过
					if(!filename.toLowerCase().endsWith(".sql")) {
						log.info("不是SQL文件,跳过:{}", fn);
						continue;
					}
					
					// 是文件...
					//log.info("是文件:{}", filename);
					String lowcase = filename.toLowerCase();
					
					// if(lowcase.endsWith("debug.sql")) {
					if(lowcase.contains("debug")) {
						log.info("debug文件,跳过... {}", filename);
						continue;
					}
					
					// 默认编码,gb2312,gb18030都是显示为gb2312
					String encoding = "gb2312";
					
					if(f.length() > 1) {
						encoding = EncodingDetect.getJavaEncode(filename).toLowerCase();
					}
					FileReader fr = FileReader.create(f, Charset.forName(encoding));
					
					
					
					// fr.readLines();
					// String texts = CollectionUtil.join(fr.readLines(), System.lineSeparator());
					String texts = fr.readString();
					//log.info("文件编码:{}, 文件:{}", encoding, filename);
					
					String md5 = MD5Util.MD5(texts);
					String owner = getOwner(file.getName());
					


					WSqlFile sqlfile = new WSqlFile();
					sqlfile.setMd5(md5)
					.setOwner(owner)
					.setFolder(file.getName())
					.setName(f.getName())
					.setLocation(filename)
					.setFileReader(fr)
					.setFile(f)
					.setEncoding(encoding)
					.setLastModified(new Timestamp(f.lastModified()))
					.setId(0)
					.setObjectName(getObjectName(texts))
					.setDate(date)
					.setLen(f.length())
					;

					// 2025-02-13  F 不再关注这个
//					if(!ENCODINGS.contains(encoding)) {
//						sqlfile.setDatatype(5);		// 数据类型 1无异常 2校验规则拦截(正则表达式匹配) 3提交冲突(当天多人同时提交) 4执行失败(语法错误) 5编码问题
//					}
					
					// 如果不是gb2312,则要警告
					List<String> infos = new ArrayList<>();
					List<String> warns = new ArrayList<>();
					List<String> errors = new ArrayList<>();
					
					
//					if(!encoding.equals("gb2312") && !encoding.equals("unicode")) {
//						//warnings.put(fn, "编码不是GB2312/gb18030或者UTF-16LE有签名,而是:" + encoding);
//						msg = "编码不是GB2312/gb18030或者UTF-16LE有签名,而是:" + encoding;
//						infos.add(msg);
//					}
//					
					//sqlfile.set
					/**
					this.logSQLFiles(f.getName(), filename, file.getName(), f);
					**/
					Boolean ok = true;
					msg = null;
					if(ok) {
						msg = checkSQLFile(texts, REGS_ERRORS, fn);
						if(msg != null) {
							ok = false;
							errors.add(msg);
							sqlfile.setDatatype(2);
						}
					}
					if(ok) {
						msg = checkSQLFile(texts, REGS_WARNINGS, fn);
						if(msg != null) {
							ok = false;
							warns.add(msg);
							sqlfile.setDatatype(2);
						}
					}
					if(ok) {
						msg = checkSQLFile(texts, REGS_INFOS, fn);
						if(msg != null) {
							ok = false;
							infos.add(msg);
							sqlfile.setDatatype(2);
						}
					}
					
					
					sqlfile.setInfos(infos)
					.setWarns(warns)
					.setErrors(errors)
					;
					rs.add(sqlfile);
				}
			}
		}
		
		
		return rs;
	}
	
	

	/**
	 * 通过正则表达式检查脚本是否匹配
	 * @return
	 */
	private static String checkSQLFile(String str, Map<String, String> regs, String filename) {
		String rs = null;
		if(str != null && regs != null) {
			for(var reg : regs.entrySet()) {
				String name = reg.getKey();
				String rule = reg.getValue();
				// List<String> ss = ReUtil.findAll(rule, str, 0);
				Boolean ok = ReUtil.contains(rule, str);
				// if(ss != null && ss.size() > 0) {
				if(ok) {
					rs = name;
					//errors.put(filename, name);
					break;
				}
			}
		}
		return rs;
	}
	
	
	public static void main(String[] args) {
		// Console.log(EncodingDetect.getJavaEncode("H:\\MYJAVA\\WTASK\\target\\MES_SQL_XH\\250103\\250120_tw\\06.alter ep_pcstyle_lookup.sql"));
		//Console.log(getOwner(""));
		
		
		// 
		var ls = read("H:\\COMPANY\\xwork\\MES_SQL_XH\\20250215_Jiang\\", top.wardyan.base.common.util.DateUtil.range(7));
		Console.log(ls);
	}
}
